package DBside;

import interfaces.QuestionQueryOperations;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import java.util.Random;

import main.SharedPref;

import com.mysql.jdbc.exceptions.MySQLTimeoutException;

import datatypes.Datatypes;
import datatypes.Datatypes.connectionType;
import datatypes.Datatypes.tableType;
import Utils.DebugUtils;
import Utils.Gameplay.Answer;
import Utils.Gameplay.QueryContext;
import Utils.Gameplay.QueryResponse;

public class QuestionQueryOperator implements QuestionQueryOperations {

	int queryCounter = 1;
	double rightRandVal = 0.1;
	double wrongRandVal = 0.1;
	boolean isFirstInChain = false;

	/* ---- flags ---- */
	boolean withEliminationCheck = false;
	boolean queryDebugMode = true;
	boolean withRandomization = false;
	boolean withFastRandomization = true;

	String SchemaName = SharedPref.schemaName;
	tableType qContext;
	tableType qConnector;
	String contextId;

	String contextTableName;
	String contextTypeId;
	String contextTypeName;

	String connectorTableName;
	String connectorTypeId;
	String connectorTypeName;

	String connectionTableName;
	String connectionContextId;
	String connectionConnectorId;
	String connectorEliminationId;

	private void init(QueryContext context) {
		qContext = context.getContext();
		qConnector = context.getConnector();
		contextId = context.getContextId();

		contextTableName = qContext.getKnownTableName();
		contextTypeId = qContext.getTypeId();
		contextTypeName = qContext.getTypeName();

		connectorTableName = qConnector.getKnownTableName();
		connectorTypeId = qConnector.getTypeId();
		connectorTypeName = qConnector.getTypeName();

		connectionType conType = Datatypes.getConnectionTableName(qContext,
				qConnector);

		connectionTableName = conType.getConnectionTableName();
		connectionContextId = conType.getContextIdName();
		connectionConnectorId = conType.getConnectorIdName();
	}

	@Override
	public QueryResponse executeQuestionQuery(QueryContext context,
			Statement stm, boolean isFirstInChain) throws NoQueryException {

		this.isFirstInChain = isFirstInChain;

		init(context);

		QueryResponse qResponse = new QueryResponse();
		qResponse.setQueryContext(context);

		String rightAnswerQuery = BuildRightAnswerQuery(context);

		ResultSet rightAnswerQueryResults = executeQuery(rightAnswerQuery, stm);

		if (rightAnswerQueryResults == null)
			return null;

		if (queryDebugMode) {
			// System.out.println("right query: \n " + rightAnswerQuery + "\n");
		}

		handleRightAnswerQueryResponse(context, rightAnswerQueryResults,
				qResponse);

		String wrongAnswersQuery = BuildWrongAnswersQuery(context);

		ResultSet wrongAnswersQueryResults = executeQuery(wrongAnswersQuery,
				stm);

		if (wrongAnswersQueryResults == null)
			return null;

		if (queryDebugMode) {
			// System.out.println("wrongs query: \n " + wrongAnswersQuery +
			// "\n");
		}

		int numOfAnswers = handleWrongAnswerQueryResponse(context,
				wrongAnswersQueryResults, qResponse);

		// in case we received less then 3 answers abort question
		if (numOfAnswers != 3 || hasDuplicates(qResponse.getAnswers())) {
			if (queryDebugMode) {
				System.out.println("less then 3 answers!");
			}

			return null;
		}

		return qResponse;
	}

	private boolean hasDuplicates(Map<Answer, Boolean> answers) {

		for (Answer answer : answers.keySet()) {
			for (Answer innerAnswer : answers.keySet()) {
				if (!answer.equals(innerAnswer)
						&& answer.getAnswer().equals(innerAnswer.getAnswer())) {
					return true;
				}
			}
		}

		return false;
	}

	private String BuildEliminationQuery(QueryContext context) {
		StringBuilder eQuery = new StringBuilder();

		eQuery.append(BuildSelectFromPart(true));
		eQuery.append("\nWHERE ");
		eQuery.append(connectionTableName + "." + connectionContextId + "="
				+ contextId);
		eQuery.append("\nAND ");
		eQuery.append(connectionTableName + "." + connectionConnectorId + "="
				+ connectorTableName + "." + connectorTypeId);
		eQuery.append("\nAND ");
		eQuery.append(connectionTableName + "." + connectionContextId + "="
				+ contextTableName + "." + contextTypeId);

		return eQuery.toString();

	}

	private String BuildWrongAnswersQuery(QueryContext context) {
		StringBuilder query = new StringBuilder();

		query.append(BuildSelectFromPart(false));

		query.append("\nWHERE ");/*
								 * query.append(connectionTableName + "." +
								 * connectionContextId + "<>" + contextId);
								 * query.append(" AND ");
								 */

		if (withEliminationCheck) {
			query.append(connectionTableName + "." + connectionConnectorId
					+ " NOT IN (" + BuildEliminationQuery(context) + ")");
			query.append(" AND ");
		}
		query.append(connectionTableName + "." + connectionConnectorId + "="
				+ connectorTableName + "." + connectorTypeId);
		query.append(" AND ");
		query.append(connectionTableName + "." + connectionContextId + "="
				+ contextTableName + "." + contextTypeId);

		if (withFastRandomization) {
			query.append(" AND ");
			query.append("RAND() <= " + wrongRandVal);
		}

		if (withRandomization) {
			query.append("\nORDER BY RAND()");
		}
		query.append("\nLIMIT 3;");

		return query.toString();
	}

	public String BuildRightAnswerQuery(QueryContext context) {
		StringBuilder query = new StringBuilder();

		query.append(BuildSelectFromPart(false));

		query.append("\nWHERE ");
		if (contextId != null) {
			query.append(connectionTableName + "." + connectionContextId + "="
					+ contextId);
			query.append(" AND ");
		}
		query.append(connectionTableName + "." + connectionConnectorId + "="
				+ connectorTableName + "." + connectorTypeId);
		query.append(" AND ");
		query.append(connectionTableName + "." + connectionContextId + "="
				+ contextTableName + "." + contextTypeId);

		if (withFastRandomization && !isFirstInChain) {
			query.append(" AND ");
			query.append("RAND() <= " + rightRandVal);
		}

		if (withRandomization && isFirstInChain) {
			query.append("\nORDER BY RAND()");
		}
		query.append("\nLIMIT 1;");

		return query.toString();

	}

	private String BuildSelectFromPart(boolean eliminiationFrom) {
		StringBuilder query = new StringBuilder();
		query.append("SELECT DISTINCT ");

		if (!eliminiationFrom) {
			query.append(contextTableName + "." + contextTypeId + " , "
					+ contextTableName + "." + contextTypeName + " , "
					+ connectorTableName + "." + connectorTypeId + " , "
					+ connectorTableName + "." + connectorTypeName);
		} else {
			query.append(connectorTableName + "." + connectorTypeId);
		}
		query.append("\nFROM ");

		query.append(SchemaName + "." + contextTableName + " , " + SchemaName
				+ "." + connectorTableName + " , " + SchemaName + "."
				+ connectionTableName);

		return query.toString();

	}

	private void handleRightAnswerQueryResponse(QueryContext qContext,
			ResultSet queryResults, QueryResponse qResponse)
			throws NoQueryException {
		qResponse.setQueryContext(qContext);
		String resultContextId, resultContextName, resultConnectorId, resultConnectorName;
		Answer answer = null;
		try {
			if (!queryResults.next()) {
				System.out.println("CONTEXT ID: " + contextId);
				throw new NoQueryException();
			}

			resultContextId = Integer.toString(queryResults.getInt(1));
			resultContextName = queryResults.getString(2);
			resultConnectorId = Integer.toString(queryResults.getInt(3));
			resultConnectorName = queryResults.getString(4);

			qResponse.setContextInfo(resultContextName);
			qResponse.setContextId(resultContextId);
			answer = new Answer(resultConnectorId, resultConnectorName);
			qResponse.addAnswer(answer, true);

			// set connector elimination id for wrong answer query
			connectorEliminationId = resultConnectorId;

		} catch (SQLException e) {

			e.printStackTrace();
		}

	}

	private int handleWrongAnswerQueryResponse(QueryContext qContext,
			ResultSet queryResults, QueryResponse qResponse)
			throws NoQueryException {

		String resultContextId, resultContextName, resultConnectorId, resultConnectorName;
		Answer answer = null;
		int numOfAnswers = 0;
		try {

			while (queryResults.next()) {
				resultContextId = Integer.toString(queryResults.getInt(1));
				resultContextName = queryResults.getString(2);
				resultConnectorId = Integer.toString(queryResults.getInt(3));
				resultConnectorName = queryResults.getString(4);

				answer = new Answer(resultConnectorId, resultConnectorName);
				qResponse.addAnswer(answer, false);
				numOfAnswers++;
			}

		} catch (SQLException e) {
			System.out.println(e);
			return 0;
		}

		return numOfAnswers;
	}

	private ResultSet executeQuery(String query, Statement stmt)
			throws NoQueryException {
		ResultSet rs = null;

		try {

			int backupTimeout = stmt.getQueryTimeout();

			if (queryDebugMode)
				System.out.println("executing query.... # " + queryCounter++);

			stmt.setQueryTimeout(3);

			rs = stmt.executeQuery(query);

			stmt.setQueryTimeout(backupTimeout);

		} catch (MySQLTimeoutException e) {
			System.out.println(e);

			if (queryDebugMode) {
				System.out.println("timeout on query: \n " + query + "\n");
			}

			return null;
		} catch (SQLException e) {
			System.out.println(e);
			return null;
		}

		// db1.closeConnection();

		return rs;
	}

}
